﻿CREATE PROCEDURE [sp_ssiscatalog].[set_parameter_defaults]
	@exec_id									BIGINT			=	NULL	
,	@exec_execution								BIT				=	NULL		
,	@exec_events								BIT				=	NULL		
,	@exec_errors								BIT				=	NULL		
,	@exec_warnings								BIT				=	NULL		
,	@exec_executable_stats						BIT				=	NULL		
,	@exec_events_packagesexcluded				NVARCHAR(MAX)	=	NULL	
,	@execs_folder_name							sysname			=	NULL
,	@execs_project_name							sysname			=	NULL
,	@execs_package_name							sysname			=	NULL
,	@execs_executed_as_name						sysname			=	NULL
,	@execs_status_desc							NVARCHAR(MAX)	=	NULL
,	@execs_minimum_execution_id					BIGINT			=	NULL
,	@execs_maximum_execution_id					BIGINT			=	NULL
,	@show_docs									BIT				=	NULL	
,	@show_docs_only								BIT				=	NULL	
,	@default_action								NVARCHAR(MAX)	=	NULL	
,	@possible_actions							[sp_ssiscatalog].[actions] READONLY
AS
BEGIN
		SET NOCOUNT ON;
				DECLARE	@vDocs	NVARCHAR(MAX)	=	N'Parameters:
	@exec_id									BIGINT				
,	@exec_execution								BIT						
,	@exec_events								BIT						
,	@exec_errors								BIT						
,	@exec_warnings								BIT						
,	@exec_executable_stats						BIT						
,	@exec_events_packagesexcluded				NVARCHAR(MAX)		
,	@execs_folder_name							sysname			
,	@execs_project_name							sysname			
,	@execs_package_name							sysname			
,	@execs_executed_as_name						sysname			
,	@execs_status_desc							NVARCHAR(MAX)	
,	@execs_minimum_execution_id					BIGINT			
,	@execs_maximum_execution_id					BIGINT			
,	@show_docs									BIT					
,	@show_docs_only								BIT					
,	@default_action								NVARCHAR(MAX)		
';
		EXEC	[sp_ssiscatalog].[show_docs]
					@docs			=	@vDocs
				,	@action			=	N'set_parameter_defaults'
				,	@show_docs		=	@show_docs;
		IF (@show_docs_only = 1)
				RETURN;

		BEGIN TRY
				/*First check that specified default action is valid*/
				IF NOT EXISTS (SELECT	*	FROM	@possible_actions WHERE [action] = @default_action)
				BEGIN
						RAISERROR('%s is not a valid action',16,1,@default_action);
				END
				ELSE
				BEGIN
						DECLARE	@vUser SYSNAME = SUSER_SNAME();
						MERGE	[sp_ssiscatalog].[parameter_default] tgt
						USING	(	SELECT	[user]							=	@vUser
									,		[action]						=	@default_action
									,		[exec_execution]				=	@exec_execution				
									,		[exec_events]					=	@exec_events				
									,		[exec_errors]					=	@exec_errors					
									,		[exec_warnings]					=	@exec_warnings					
									,		[exec_executable_stats]			=	@exec_executable_stats			
									,		[exec_events_packagesexcluded]	=	@exec_events_packagesexcluded	
									,		[execs_folder_name]				=	@execs_folder_name				
									,		[execs_project_name]			=	@execs_project_name			
									,		[execs_package_name]			=	@execs_package_name			
									,		[execs_executed_as_name]		=	@execs_executed_as_name		
									,		[execs_status_desc]				=	@execs_status_desc				
									,		[execs_minimum_execution_id]	=	@execs_minimum_execution_id	
									,		[execs_maximum_execution_id]	=	@execs_maximum_execution_id	
									,		[show_docs]						=	@show_docs						
									,		[show_docs_only]				=	@show_docs_only						
								)src
							ON	tgt.[user] = src.[user]
						WHEN MATCHED THEN
								UPDATE
								SET				tgt.[action]						=	src.[action]						
										,		tgt.[exec_execution]				=	src.[exec_execution]				
										,		tgt.[exec_events]					=	src.[exec_events]					
										,		tgt.[exec_errors]					=	src.[exec_errors]					
										,		tgt.[exec_warnings]					=	src.[exec_warnings]					
										,		tgt.[exec_executable_stats]			=	src.[exec_executable_stats]			
										,		tgt.[exec_events_packagesexcluded]	=	src.[exec_events_packagesexcluded]	
										,		tgt.[execs_folder_name]				=	src.[execs_folder_name]				
										,		tgt.[execs_project_name]			=	src.[execs_project_name]			
										,		tgt.[execs_package_name]			=	src.[execs_package_name]			
										,		tgt.[execs_executed_as_name]		=	src.[execs_executed_as_name]		
										,		tgt.[execs_status_desc]				=	src.[execs_status_desc]				
										,		tgt.[execs_minimum_execution_id]	=	src.[execs_minimum_execution_id]	
										,		tgt.[execs_maximum_execution_id]	=	src.[execs_maximum_execution_id]	
										,		tgt.[show_docs]						=	src.[show_docs]						
										,		tgt.[show_docs_only]				=	src.[show_docs_only]				
						WHEN NOT MATCHED THEN
								INSERT(		[user]
										,	[action]
										,	[exec_execution]
										,	[exec_events]
										,	[exec_errors]
										,	[exec_warnings]
										,	[exec_executable_stats]
										,	[exec_events_packagesexcluded]
										,	[execs_folder_name]
										,	[execs_project_name]
										,	[execs_package_name]
										,	[execs_executed_as_name]
										,	[execs_status_desc]
										,	[execs_minimum_execution_id]
										,	[execs_maximum_execution_id]	
										,	[show_docs]
										,	[show_docs_only]
								)
								VALUES(		src.[user]
										,	src.[action]
										,	src.[exec_execution]
										,	src.[exec_events]
										,	src.[exec_errors]
										,	src.[exec_warnings]
										,	src.[exec_executable_stats]
										,	src.[exec_events_packagesexcluded]
										,	src.[execs_folder_name]
										,	src.[execs_project_name]
										,	src.[execs_package_name]
										,	src.[execs_executed_as_name]
										,	src.[execs_status_desc]
										,	src.[execs_minimum_execution_id]
										,	src.[execs_maximum_execution_id]	
										,	src.[show_docs]
										,	src.[show_docs_only]
								)
						;
						SELECT	[comment]='your defaults are -->',pd.[action], pd.[exec_execution], pd.[exec_events], pd.[exec_errors], pd.[exec_warnings], pd.[exec_executable_stats], pd.[exec_events_packagesexcluded], pd.[execs_folder_name], pd.[execs_project_name], pd.[execs_package_name], pd.[execs_executed_as_name], pd.[execs_status_desc], pd.[execs_minimum_execution_id], pd.[execs_maximum_execution_id], pd.[configure_collect_usage_stats], pd.[show_docs], pd.[show_docs_only]
						FROM	[sp_ssiscatalog].[parameter_default]pd
						WHERE	pd.[user] = @vUser;
				END
		END TRY
		BEGIN CATCH
				THROW;
		END CATCH
END